/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package bulkmailer.email.addr.manager;

import bulkmailer.beans.EmailAddress;
import bulkmailer.db.DBConnector;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.TreeSet;
import org.apache.log4j.Logger;

/**
 *
 * @author ravi
 */
public class EMailAddressManager {

    Connection con = null;

    public EMailAddressManager() {
        try {
            this.con = DBConnector.getConnection(DBConnector.SCHEMA_NAME);
        } catch (Exception ex) {
            Logger.getRootLogger().error(ex);
        }
    }

    public TreeSet<String> getAllAddress() {
        TreeSet<String> addrSet = new TreeSet<String>();
        String query = "SELECT * from mail_record";
        PreparedStatement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = con.prepareStatement(query);
            resultSet = stmt.executeQuery();
            while (resultSet.next() && resultSet != null) {
                addrSet.add(resultSet.getString("email_id"));
            }
        } catch (SQLException ex) {
            Logger.getRootLogger().error(ex);
        }
        return addrSet;
    }

    public TreeSet<String> getAllDomains() {
        TreeSet<String> domainSet = new TreeSet<String>();
        String query = "SELECT * from mail_record WHERE active = 'Y'";
        PreparedStatement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = con.prepareStatement(query);
            Logger.getRootLogger().debug(query);
            resultSet = stmt.executeQuery();
            int indexOfAt = -1;
            String emailId = null;
            while (resultSet.next() && resultSet != null) {
                try {
                    emailId = resultSet.getString("email_id");
                    indexOfAt = emailId.indexOf("@");
                    domainSet.add(emailId.substring(indexOfAt + 1).toLowerCase());
                } catch (StringIndexOutOfBoundsException ex) {
                    System.out.println("Error found: " + emailId);
                }

            }
        } catch (SQLException ex) {
            Logger.getRootLogger().error(ex);
        }
        return domainSet;
    }

    public TreeSet<String> getAddressesOfDomain(TreeSet<String> domains) {
        Logger.getRootLogger().debug("getting mails of domains");
        TreeSet<String> address = new TreeSet<String>();
        Iterator it = domains.iterator();
        String query = "SELECT * FROM BulkMailer.mail_record where email_id LIKE \"%@" + it.next().toString() + "\"";
        for (; it.hasNext();) {
            query += " OR email_id LIKE \"%@" + it.next().toString() + "\"";//%yahoo.com" or email_id like "%orkut.com"
        }
        query = query + "AND active = 'Y'";
        Logger.getRootLogger().debug(query);
        PreparedStatement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = con.prepareStatement(query);
            resultSet = stmt.executeQuery();
            String emailId = null;
            while (resultSet.next() && resultSet != null) {
                try {
                    emailId = resultSet.getString("email_id");
                    address.add(emailId);
                } catch (StringIndexOutOfBoundsException ex) {
                    Logger.getRootLogger().error(ex.toString());
                }

            }
        } catch (SQLException ex) {
            Logger.getRootLogger().error(ex);
        }
        return address;
    }

    public void pushMail(TreeSet<String> set) {
        try {
            String email = null;
            String query = "INSERT INTO mail_record(email_id,active,last_mail_id) values(?,'Y',0)";
            PreparedStatement stmt = null;
            stmt = con.prepareStatement(query);
            Iterator iter = set.iterator();
            for (; iter.hasNext();) {
                email = iter.next().toString();
                if (checkForDuplicate(email) == false) {
                    stmt.setString(1, email);
                    stmt.execute();
                }
            }
        } catch (SQLException ex) {
            Logger.getRootLogger().error(ex);
        }

    }

    public boolean checkForDuplicate(String email) {
        boolean isDuplicate = false;
        String query = "SELECT * from mail_record WHERE email_id = ?";
        PreparedStatement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = con.prepareStatement(query);
            stmt.setString(1, email);
            resultSet = stmt.executeQuery();
            if (resultSet.next() || resultSet == null) {
                Logger.getRootLogger().debug("duplicate found for: " + email);
                isDuplicate = true;
            }
        } catch (SQLException ex) {
            Logger.getRootLogger().error(ex);
        }
        return isDuplicate;
    }

    public ArrayList<EmailAddress> getAllAddressesWithStatus() {
        EmailAddress emailAddress = null;
        ArrayList<EmailAddress> addressList = null;
        String query = "SELECT * from mail_record WHERE active = 'Y'";
        PreparedStatement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = con.prepareStatement(query);
            resultSet = stmt.executeQuery();
            if (resultSet == null) {
                Logger.getRootLogger().error("result is null");
            }
            addressList = new ArrayList<EmailAddress>();
            while (resultSet.next() && resultSet != null) {
                emailAddress = new EmailAddress();
                emailAddress.setEmailAddress(resultSet.getString("email_id"));
                emailAddress.setStatus(resultSet.getString("active"));
                emailAddress.setLastMailSent(resultSet.getInt("last_mail_id"));
                addressList.add(emailAddress);
            }
        } catch (SQLException ex) {
            Logger.getRootLogger().error(ex);
        }
        return addressList;
    }
    
    public ArrayList<EmailAddress> getAllAddressesWithStatus(String domain) {
        EmailAddress emailAddress = null;
        ArrayList<EmailAddress> addressList = null;
        String query = "SELECT * from mail_record WHERE active = 'Y' AND email_id LIKE \"%@"+domain+"\"";
        PreparedStatement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = con.prepareStatement(query);
            Logger.getRootLogger().debug(query);
            resultSet = stmt.executeQuery();
            if (resultSet == null) {
                Logger.getRootLogger().error("result is null");
            }
            addressList = new ArrayList<EmailAddress>();
            while (resultSet.next() && resultSet != null) {
                emailAddress = new EmailAddress();
                emailAddress.setEmailAddress(resultSet.getString("email_id"));
                emailAddress.setStatus(resultSet.getString("active"));
                emailAddress.setLastMailSent(resultSet.getInt("last_mail_id"));
                addressList.add(emailAddress);
            }
        } catch (SQLException ex) {
            Logger.getRootLogger().error(ex);
        }
        return addressList;
    }

    public void deleteOnDomain(TreeSet<String> domainsArray) {
        TreeSet<String> address = new TreeSet<String>();
        Iterator it = domainsArray.iterator();
        String query = "UPDATE mail_record set active = 'N' WHERE email_id LIKE \"%@" + it.next().toString() + "\"";
        for (; it.hasNext();) {
            query += " OR email_id LIKE \"%@" + it.next().toString() + "\"";//%yahoo.com" or email_id like "%orkut.com"
        }
        Logger.getRootLogger().debug(query);
        PreparedStatement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = con.prepareStatement(query);
            stmt.execute();
        } catch (SQLException ex) {
            Logger.getRootLogger().error(ex);
        }
    }
}
